SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


CREATE PROCEDURE dbo.[DnnProduct_GetAllUsers]
	@PortalID        int,
	@PageIndex       int,
	@PageSize        INT,
	@IncludeDeleted  bit,
	@SuperUsersOnly  bit	
AS
	BEGIN
		-- Set the page bounds
		DECLARE 
			@PageLowerBound INT, 
			@PageUpperBound INT, 
			@RowsToReturn int

		exec dbo.DnnProduct_CalculatePagingInformation @PageIndex, @PageSize, @RowsToReturn output, @PageLowerBound output, @PageUpperBound output

		if @PortalID is null
			begin
				with [tmpUsers] as (
					select U.*, row_number() over (order by U.UserName) AS rowid
						FROM dbo.DnnProduct_vw_Users u
						WHERE U.IsDeleted <= CASE @IncludeDeleted WHEN 0 THEN 0 ELSE 1 END
							--less than equal done to cover IsDeleted in (1,0) for IncludeDeleted...else just IsDeleted = 0
							  AND U.IsSuperUser >= CASE @SuperUsersOnly WHEN 1 THEN 1 ELSE 0 END
				)
				select * from [tmpUsers]
					where rowid > @PageLowerBound and rowid < @PageUpperBound
					order by rowid
			end 
		else 
			begin
				with [tmpUsers] as (
					select U.*, row_number() over (order by U.UserName) AS rowid
						FROM dbo.DnnProduct_vw_Users u
						WHERE U.PortalID = @PortalID AND U.IsDeleted <= CASE @IncludeDeleted WHEN 0 THEN 0 ELSE 1 END
						  AND U.IsSuperUser >= CASE @SuperUsersOnly WHEN 1 THEN 1 ELSE 0 END
				)
				select * from [tmpUsers]
					where rowid > @PageLowerBound and rowid < @PageUpperBound
					order by rowid
			end

		set rowcount 0
 
		if @PortalId is null
			begin
				SELECT COUNT(*) as TotalRecords
					FROM   dbo.DnnProduct_Users as U
					WHERE U.IsDeleted <= CASE @IncludeDeleted WHEN 0 THEN 0 ELSE 1 END
				               AND U.IsSuperUser >= CASE @SuperUsersOnly WHEN 1 THEN 1 ELSE 0 END
						AND NOT EXISTS (
							select 1 from dbo.DnnProduct_UserPortals UP where U.UserId = UP.UserId
						)
			end 
		else 
			begin
				SELECT COUNT(*) as TotalRecords
					FROM   dbo.DnnProduct_Users U
					INNER JOIN dbo.DnnProduct_UserPortals UP ON U.UserId = UP.UserId
						WHERE UP.PortalId = @PortalId
							AND UP.IsDeleted <= CASE @IncludeDeleted WHEN 0 THEN 0 ELSE 1 END
							AND U.IsSuperUser >= CASE @SuperUsersOnly WHEN 1 THEN 1 ELSE 0 END
			end
	END
GO
